# -*- coding: UTF-8 -*-
# Author: Damon(wuud1)
# CreateDate: 
# Message:
from base_class import BaseClass
from pprint import pprint
import json
import redis
import time

class GetCarsPartNum(BaseClass):

    def __init__(self, **kwargs):
        self.src_db = kwargs.get("src_db")
        self.dest_db = kwargs.get("dest_db")
        self.insert_table = kwargs.get("insert_table")
        self.src_conn, self.src_curs = self.get_conn_curs(db_config=self.src_db, type='dict')
        self.dest_conn, self.dest_curs = self.get_conn_curs(db_config=self.dest_db, type='dict')
        self.num = 0
        self.cn_label = {}  # 中文label对照字典
        self.en_label = {}  # 英文label对照字典
        self.all_part_dict = {}
        self.all_pid_info_dict = {}
        self.r = redis.Redis(host='localhost', port=6379, db=0).pipeline()  # redis链接,用于断点续爬
        self.redis_set = "part_num_set"


    def get_mid_list(self):
        sql = '''select `maingroup`,`mid` from `cars_structure_collection`'''
        self.src_curs.execute(sql)
        data = self.src_curs.fetchall()
        return [{"maingroup":i.get("maingroup"), "mid":i.get("mid")} for i in data]

    def get_all_part_dict(self):
        mid_list = self.get_mid_list()
        self.all_part_dict = {i.get("mid"):[] for i in mid_list}

        sql = '''select distinct trim(F2),trim(F3),trim(F4),trim(F5),trim(F6),trim(F7) from pcodenes where length(trim(F5))>3'''
        self.src_curs.execute(sql)
        for data in self.src_curs.fetchall():
            sql_mid = data.get("trim(F2)")+'_'+data.get("trim(F3)")+'_'+data.get("trim(F4)")
            sql_data = {"F5":data.get("trim(F5)"),"F6":data.get("trim(F6)"),"F7":data.get("trim(F7)")}
            if sql_mid in self.all_part_dict:
                self.all_part_dict[sql_mid].append(sql_data)
        # pprint(self.all_part_dict['B10RS_401_A 02'])

    def get_all_pid_info_dict(self):
        cid_pcode_sql = u'''select distinct trim(F2),trim(F3) from catalog'''
        self.src_curs.execute(cid_pcode_sql)
        self.all_pid_info_dict = {i.get("trim(F2)")+i.get("trim(F3)"):[] for i in self.src_curs.fetchall()}

        # 构造catalog完整字典, f2+f3为key,其余构成字典作为value
        pid_info_sql = u'''select distinct trim(F2),trim(F3),trim(F5),trim(F6),trim(F7),trim(F8),trim(F9),trim(F10),trim(F11),trim(F12),trim(F13),trim(F14),trim(F15),trim(F16),trim(F17) from catalog;'''
        self.src_curs.execute(pid_info_sql)
        for i in self.src_curs.fetchall():
            k = i.get("trim(F2)")+i.get("trim(F3)")
            v = {"F5": i.get("trim(F5)"),
                 "F6": i.get("trim(F6)"),
                 "F7": i.get("trim(F7)"),
                 "F8": i.get("trim(F8)"),
                 "F9": i.get("trim(F9)"),
                 "F10": i.get("trim(F10)"),
                 "F11": i.get("trim(F11)"),
                 "F12": i.get("trim(F12)"),
                 "F13": i.get("trim(F13)"),
                 "F14": i.get("trim(F14)"),
                 "F15": i.get("trim(F15)"),
                 "F16": i.get("trim(F16)"),
                 "F17": i.get("trim(F17)"),
                 }
            self.all_pid_info_dict[k].append(v)

    def get_part_list(self, mid):
        # sql = '''select distinct trim(F5),trim(F6),trim(F7) from pcodenes t where trim(f2)="{}" and trim(f3)="{}" and trim(f4)="{}";'''.format(cid, kid, img_group)
        # self.src_curs.execute(sql)
        # data = self.src_curs.fetchall()
        data = self.all_part_dict.get(mid, [])
        if len(data) == 0:
            return [],{}
        part_code_list = set([i.get("F5") for i in data])  # 所对应的所有part_code去重结果
        part_imgPoints_dict = {i:[] for i in part_code_list}  # 每个part_code 所对应的描点信息
        for part in data:
            part_code = part.get("F5")
            part_x = part.get("F6")
            part_y = part.get("F7")
            part_imgPoints_dict[part_code].append({"x": part_x, "y": part_y})

        return part_code_list, part_imgPoints_dict

    def get_label(self, part_code):
        if part_code in self.cn_label:
            label = self.cn_label[part_code]
        elif part_code in self.en_label:
            label = self.en_label[part_code]
        else:
            label = ""
        return label

    def get_pid_info_list(self, cid, part_code):
        # sql = '''select distinct * from catalog where trim(f2)="{}" and trim(f3)="{}"'''.format(cid, part_code)
        # self.src_curs.execute(sql)
        # data = self.src_curs.fetchall()
        k = cid+part_code
        data = self.all_pid_info_dict.get(k, [])

        pid_info_list = [{"pid":i.get("F5").strip(), "extra_info":{"year":i.get("F6").strip()+'-'+i.get("F7").strip(),"v_type":i.get("F9").strip(), "F8":i.get("F8") if i.get("F8") else "", "F10":i.get("F10") if i.get("F10") else "", "F12":i.get("F12") if i.get("F12") else "", "F14":i.get("F14") if i.get("F14") else "","F15":i.get("F15") if i.get("F15") else "","F16":i.get("F16") if i.get("F16") else "","F17":i.get("F17") if i.get("F17") else ""}, "rid":i.get("F11").strip(), "model":i.get("F13")} for i in data]
        return pid_info_list

    def get_cars_part_info(self, maingroup, cid, kid, img_group):
        mid = cid+'_'+kid+'_'+img_group
        _list= []
        psort = 0
        # 先取mid下对应的零件列表
        part_code_list, part_imgPoints_dict = self.get_part_list(mid)
        # print(part_code_list)
        # pprint(part_imgPoints_dict)
        # {part_code: [{"x":x,"y":y},{"x":x,"y":y}],part_code: [{},{}]...}
        for part_code in part_code_list:
            # 根据part_code 获取其对应的label, 没有中文则填写英文
            label = self.get_label(part_code)
            pnum = part_code
            # 根据cid, part_code获取其对应的零件oe号(或许是多个)
            pid_info_list = self.get_pid_info_list(cid, part_code)
            for pid_info in pid_info_list:
                psort += 1
                pid = pid_info.get("pid")
                extra_info = pid_info.get("extra_info")
                extra_info["imgPoints"]=part_imgPoints_dict[part_code]
                model = pid_info.get("model")
                rid = pid_info.get("rid")
                updatetime = 'now()'
                createtime = 'now()'
                _dict = dict(
                    cid=cid,
                    num=maingroup,
                    maingroup=maingroup,
                    mid=mid,
                    brandCode="nissan",
                    pnum=pnum,
                    psort=psort,
                    pid=pid,
                    label=label,
                    quantity='',
                    model=model,
                    rid=rid,
                    extra_info=json.dumps(extra_info),
                    updatetime=updatetime,
                    createtime=createtime
                )
                # pprint(_dict)
                _list.append(_dict)
        return _list
    def get_partcode_label(self):
        '''获取零件代码与中英文名的对照字典'''
        cn_sql = u'''select distinct trim(PCODE), trim(PTEXT_CN) from patcode where PTEXT_CN!="";'''
        self.src_curs.execute(cn_sql)
        cn_data = self.src_curs.fetchall()
        self.cn_label = {i.get("trim(PCODE)"): i.get("trim(PTEXT_CN)") for i in cn_data}

        # 获取英文label对照字典, 如果中文对照没有,则来取英文
        en_sql = u'''select distinct trim(PCODE), trim(PTEXT) from patcode where PTEXT_CN="";'''
        self.src_curs.execute(en_sql)
        en_data = self.src_curs.fetchall()
        self.en_label = {i.get("trim(PCODE)"): i.get("trim(PTEXT)") for i in en_data}

    def run(self):
        print("开始运行...")
        print("开始构造大型字典...")
        self.get_all_part_dict()
        self.get_all_pid_info_dict()
        print("构造大型字典完毕...")
        self.get_partcode_label()
        mid_list = self.get_mid_list()
        for mid_dict in mid_list:
            maingroup = mid_dict.get("maingroup")
            mid = mid_dict.get("mid")
            # mid = "B10RS_401_A 02"  # 测试用mid
            # 断点代码块
            self.r.sismember(self.redis_set, mid)
            if self.r.execute()[0]:
                continue
            self.r.sadd(self.redis_set, mid)

            code_list = mid.split('_')
            cid=code_list[0]
            kid=code_list[1]
            img_group=code_list[2]
            try:
                cars_part_list = self.get_cars_part_info(maingroup, cid, kid, img_group)
            except Exception as e:
                print("*"*20, mid)
                raise e

            for cars_part in cars_part_list:
                try:
                    insert_sql = self.construct_insert_sql(self.insert_table, cars_part)
                    # pprint(insert_sql)
                    self.dest_curs.execute(insert_sql)
                except Exception as e:
                    print("*"*20, mid)
                    raise e
                self.num += 1
                if self.num % 1000 == 0:
                    self.dest_conn.commit()
                    print("提交insert缓存, 当前数量: {}".format(self.num))
            self.r.execute()  # 提交redis
        self.dest_conn.commit()
        print("最终提交, 总数据量: {}".format(self.num))


if __name__ == '__main__':
    src_db = dict(
        host="127.0.0.1",
        port=3306,
        user="root",
        passwd="123456",
        db="nissan",
        charset="utf8",
    )
    dest_db = dict(
        host="127.0.0.1",
        port=3306,
        user="root",
        passwd="123456",
        db="nissan",
        charset="utf8",
    )
    insert_table = 'cars_part_num'
    tools = GetCarsPartNum(src_db=src_db, dest_db=dest_db, insert_table=insert_table)
    tools.run()
    # st = time.time()
    # tools.get_all_part_dict()
    # en = time.time()
    # print(en-st)